/*
 * AddressDao.java
 *
 */
package com.sun.demo.addressbook.db;

import com.sun.demo.addressbook.Address;
import com.sun.demo.addressbook.PeopleListEntry;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 *
 */
public class AddressDao {
    private LppDbSetup lppDbSetup;
    private Connection dbConnection;
    private Properties dbProperties;
    private boolean isConnected;
    private String dbName;
    private PreparedStatement stmtCreateAddress;
    private PreparedStatement stmtUpdateAddress;
    private PreparedStatement stmtGetListEntries;
    private PreparedStatement stmtGetAddress;
    private PreparedStatement stmtDeleteAddress;
    
    public AddressDao() {
        lppDbSetup = LppDbSetup.getLppDbSetup();
        System.out.println(lppDbSetup.getDatabaseDiskLocation());
        System.out.println(lppDbSetup.getDatabaseUrl());
    }
    
    public boolean connect() {
        String dbUrl = lppDbSetup.getDatabaseUrl();
        try {
            dbConnection = DriverManager.getConnection(dbUrl, lppDbSetup.getDatabaseProperties());
            stmtCreateAddress = dbConnection.prepareStatement(strCreateAddress, Statement.RETURN_GENERATED_KEYS);
            stmtUpdateAddress = dbConnection.prepareStatement(strUpdateAddress);
            stmtGetAddress = dbConnection.prepareStatement(strGetAddress);
            stmtDeleteAddress = dbConnection.prepareStatement(strDeleteAddress);
            isConnected = dbConnection != null;
        } catch (SQLException ex) {
            isConnected = false;
            ex.printStackTrace();
        }
        return isConnected;
    }
    
    public void disconnect() {
        if(isConnected) {
            String dbUrl = lppDbSetup.getDatabaseUrl();
            dbProperties.put("shutdown", "true");
            try {
                DriverManager.getConnection(dbUrl, dbProperties);
            } catch (SQLException ex) {
            }
            isConnected = false;
        }
    }
    
    public int createAddress(Address record) {
        int id = -1;
        try {
            stmtCreateAddress.clearParameters();
            
            stmtCreateAddress.setString(1, record.getLastName());
            stmtCreateAddress.setString(2, record.getFirstName());
            stmtCreateAddress.setString(3, record.getMiddleName());
            stmtCreateAddress.setString(4, record.getPhone());
            stmtCreateAddress.setString(5, record.getOfficePhone());
            stmtCreateAddress.setString(6, record.getCellPhone());
            stmtCreateAddress.setString(7, record.getEmail());
            stmtCreateAddress.setString(8, record.getAddress1());
            stmtCreateAddress.setString(9, record.getCity());
            stmtCreateAddress.setString(10, record.getState());
            stmtCreateAddress.setString(11, record.getPostalCode());
            stmtCreateAddress.setInt(12, record.getAge());
            stmtCreateAddress.setString(13, record.getHeightFeet());
            stmtCreateAddress.setString(14, record.getHeightInches());
            stmtCreateAddress.setBoolean(15, record.getOwnsCostume());
            stmtCreateAddress.setBoolean(16, record.getCostumeAtHome());
            stmtCreateAddress.setString(17, record.getCostumeList());
            stmtCreateAddress.setString(18, record.getPart2006());
            stmtCreateAddress.setBoolean(19, record.getSoloAudition());
            stmtCreateAddress.setString(20, record.getLowVocalNote());
            stmtCreateAddress.setString(21, record.getHighVocalNote());
            stmtCreateAddress.setInt(22, record.getExperienceLevel());
            stmtCreateAddress.setInt(23, record.getAssignedPart());
            stmtCreateAddress.setString(24, record.getNotes());
            
            int rowCount = stmtCreateAddress.executeUpdate();
            ResultSet results = stmtCreateAddress.getGeneratedKeys();
            if (results.next()) {
                id = results.getInt(1);
            }
        } catch(SQLException sqle) {
            sqle.printStackTrace();
        }
        return id;
    }
    
    public boolean updateAddress(Address record) {
        boolean bEdited = false;
        try {
            stmtUpdateAddress.clearParameters();
            
            stmtUpdateAddress.setString(1, record.getLastName());
            stmtUpdateAddress.setString(2, record.getFirstName());
            stmtUpdateAddress.setString(3, record.getMiddleName());
            stmtUpdateAddress.setString(4, record.getPhone());
            stmtUpdateAddress.setString(5, record.getOfficePhone());
            stmtUpdateAddress.setString(6, record.getCellPhone());
            stmtUpdateAddress.setString(7, record.getEmail());
            stmtUpdateAddress.setString(8, record.getAddress1());
            stmtUpdateAddress.setString(9, record.getCity());
            stmtUpdateAddress.setString(10, record.getState());
            stmtUpdateAddress.setString(11, record.getPostalCode());
            stmtUpdateAddress.setInt(12, record.getAge());
            stmtUpdateAddress.setString(13, record.getHeightFeet());
            stmtUpdateAddress.setString(14, record.getHeightInches());
            stmtUpdateAddress.setBoolean(15, record.getOwnsCostume());
            stmtUpdateAddress.setBoolean(16, record.getCostumeAtHome());
            stmtUpdateAddress.setString(17, record.getCostumeList());
            stmtUpdateAddress.setString(18, record.getPart2006());
            stmtUpdateAddress.setBoolean(19, record.getSoloAudition());
            stmtUpdateAddress.setString(20, record.getLowVocalNote());
            stmtUpdateAddress.setString(21, record.getHighVocalNote());
            stmtUpdateAddress.setInt(22, record.getExperienceLevel());
            stmtUpdateAddress.setInt(23, record.getAssignedPart());
            stmtUpdateAddress.setString(24, record.getNotes());
            stmtUpdateAddress.setInt(25, record.getId());
            
            stmtUpdateAddress.executeUpdate();
            bEdited = true;
        } catch(SQLException sqle) {
            sqle.printStackTrace();
        }
        return bEdited;
    }
    
    public boolean deleteAddress(int id) {
        boolean bDeleted = false;
        try {
            stmtDeleteAddress.clearParameters();
            stmtDeleteAddress.setInt(1, id);
            stmtDeleteAddress.executeUpdate();
            bDeleted = true;
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return bDeleted;
    }
    
    public boolean deleteAddress(Address record) {
        int id = record.getId();
        return deleteAddress(id);
    }
    
    public List<PeopleListEntry> getListEntries() {
        List<PeopleListEntry> listEntries = new ArrayList<PeopleListEntry>();
        Statement queryStatement = null;
        ResultSet results = null;
        
        try {
            queryStatement = dbConnection.createStatement();
            results = queryStatement.executeQuery(strGetListEntries);
            while(results.next()) {
                int id = results.getInt(1);
                String lName = results.getString(2);
                String fName = results.getString(3);
                
                PeopleListEntry entry = new PeopleListEntry(lName, fName, id);
                listEntries.add(entry);
            }
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return listEntries;
    }
    
    public Address getAddress(int index) {
        Address address = null;
        try {
            stmtGetAddress.clearParameters();
            stmtGetAddress.setInt(1, index);
            ResultSet result = stmtGetAddress.executeQuery();
            if (result.next()) {
                String lastName = result.getString("LASTNAME");
                String firstName = result.getString("FIRSTNAME");
                String middleName = result.getString("MIDDLENAME");
                String phone = result.getString("PHONE");
                String officePhone = result.getString("OFFICEPHONE");
                String cellPhone = result.getString("CELLPHONE");
                String email = result.getString("EMAIL");
                String add1 = result.getString("ADDRESS1");
                String city = result.getString("CITY");
                String state = result.getString("STATE");
                String postalCode = result.getString("POSTALCODE");
                int age = result.getInt("AGE");
                String heightFeet = result.getString("HEIGHTFEET");
                String heightInches = result.getString("HEIGHTINCHES");
                boolean ownsCostume = result.getBoolean("OWNSCOSTUME");
                boolean costumeAtHome = result.getBoolean("COSTUMEATHOME");
                String costumeList = result.getString("COSTUMELIST");
                String part2006 = result.getString("PART2006");
                boolean soloAudition = result.getBoolean("SOLOAUDITION");
                String lowVocalNote = result.getString("LOWVOCALNOTE");
                String highVocalNote = result.getString("HIGHVOCALNOTE");
                int experienceLevel = result.getInt("EXPERIENCELEVEL");
                int assignedPart = result.getInt("ASSIGNEDPART");
                String notes = result.getString("NOTES");
                int id = result.getInt("ID");
                address = new Address(lastName, firstName, middleName, phone,
                        officePhone, cellPhone, email, add1, city, state,
                        postalCode, age, heightFeet, heightInches, 
                        ownsCostume, costumeAtHome, costumeList,
                        part2006, soloAudition, lowVocalNote, highVocalNote,
                        experienceLevel, assignedPart, notes, id);
            }
        } catch(SQLException sqle) {
            sqle.printStackTrace();
        }
        return address;
    }
    
    private static final String strGetAddress =
            "SELECT * FROM APP.ADDRESS " +
            "WHERE ID = ?";
    
    private static final String strCreateAddress =
            "INSERT INTO APP.ADDRESS " +
            "   (LASTNAME, FIRSTNAME, MIDDLENAME, PHONE, OFFICEPHONE, CELLPHONE, EMAIL, " +
            "    ADDRESS1, " +
            "    CITY, STATE, POSTALCODE, AGE, HEIGHTFEET, HEIGHTINCHES, " +
            "    OWNSCOSTUME, COSTUMEATHOME, COSTUMELIST, PART2006, SOLOAUDITION, " +
            "    LOWVOCALNOTE, HIGHVOCALNOTE, EXPERIENCELEVEL, ASSIGNEDPART, NOTES) " +
            "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    
    private static final String strUpdateAddress =
            "UPDATE APP.ADDRESS " +
            "SET LASTNAME = ?, " +
            "    FIRSTNAME = ?, " +
            "    MIDDLENAME = ?, " +
            "    PHONE = ?, " +
            "    OFFICEPHONE = ?, " +
            "    CELLPHONE = ?, " +
            "    EMAIL = ?, " +
            "    ADDRESS1 = ?, " +
            "    CITY = ?, " +
            "    STATE = ?, " +
            "    POSTALCODE = ?, " +
            "    AGE = ?, " +
            "    HEIGHTFEET = ?, " +
            "    HEIGHTINCHES = ?, " +
            "    OWNSCOSTUME = ?, " +
            "    COSTUMEATHOME = ?, " +
            "    COSTUMELIST = ?, " +
            "    PART2006 = ?, " +
            "    SOLOAUDITION = ?, " +
            "    LOWVOCALNOTE = ?, " +
            "    HIGHVOCALNOTE = ?, " +
            "    EXPERIENCELEVEL = ?, " +
            "    ASSIGNEDPART = ?, " +
            "    NOTES = ? " +
            "WHERE ID = ?";
    
    private static final String strGetListEntries =
            "SELECT ID, LASTNAME, FIRSTNAME FROM APP.ADDRESS "  +
            "ORDER BY LASTNAME ASC";
    
    private static final String strDeleteAddress =
            "DELETE FROM APP.ADDRESS " +
            "WHERE ID = ?";
    
    public static void main(String[] args) {
        AddressDao db = new AddressDao();
        db.connect();
        db.disconnect();
    }
}
